package com.example.xiaomi.mapper;

import com.example.xiaomi.entity.Useranalysis;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UseranalysisMapper {
    @Select("SELECT date, activeacount FROM (SELECT date, COUNT(*) AS activeacount FROM film GROUP BY date) AS subquery ORDER BY date ASC;")
    List<Useranalysis> findAllactive();

    @Select("SELECT * FROM xiaomi.sentiment_analysis_results;")
    List<Useranalysis> findSentiment();

    @Select("SELECT * FROM xiaomi.sentimentwords_analysis_results ORDER BY acount DESC limit 50;")
    List<Useranalysis> findSentimentwords();

    @Select("SELECT ip, COUNT(*) AS ip_count FROM film GROUP BY ip;")
    List<Useranalysis> findAllip();

    @Select("SELECT user_type, COUNT(*) AS label_count FROM film WHERE LENGTH(user_type) > 5 AND LENGTH(user_type) < 26 GROUP BY user_type order by label_count desc limit 100;")
    List<Useranalysis> findLabel();

    @Select("select gender,count(*) as gender_count from film WHERE LENGTH(gender) > 1 AND LENGTH(gender) < 5 group by gender;")
    List<Useranalysis> findGender();

    @Select("SELECT age, COUNT(*) AS age_count FROM film WHERE age > 1 GROUP BY age;")
    List<Useranalysis> findAge();

}
